package com.fau.cm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.cm.entity.MedicalAdmin;
import com.fau.cm.entity.MedicalCallRecord;
import com.fau.cm.entity.MedicalCallRelay;
import com.fau.cm.entity.MedicalKeyValue;
import com.fau.cm.entity.MedicalPreferentialPlan;
import com.fau.cm.entity.MedicalTariff;
import com.fau.cm.service.IBillingManagerService;
import com.fau.cm.util.Page;
import com.fau.cm.util.ParamUtil;
import com.fau.cm.util.SystemConfig;
import com.fau.cm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class BillingManagerServiceImpl extends BaseService implements IBillingManagerService {

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void insertPreferentialPlan(MedicalPreferentialPlan preferentialPlan) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(preferentialPlan);
		if(errorInfo.size() <= 0) {
			MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
			preferentialPlan.setEnterpriseId(admin.getEnterpriseId());
			this.iBaseDao.saveObject(preferentialPlan);
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void insertTariff(MedicalTariff tariff) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(tariff);
		if(errorInfo.size() <= 0) {
			MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
			if(StringUtils.isNotBlank(tariff.getPreferentiaId())){
				MedicalPreferentialPlan preferentialPlan = (MedicalPreferentialPlan) this.iBaseDao.getObject(MedicalPreferentialPlan.class, tariff.getPreferentiaId());
				tariff.setPreferential(preferentialPlan);
			}
			tariff.setEid(admin.getEnterpriseId());
			this.iBaseDao.saveObject(tariff);
		}
	}

	@Override
	public Page findPreferentialPlanByPage(Map<String, Object> map,
			MedicalPreferentialPlan preferentialPlan, String eid) {
		StringBuilder statusSql = new StringBuilder("from MedicalPreferentialPlan a where a.enterpriseId=? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalPreferentialPlan a where a.enterpriseId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(admin.getEnterpriseId());
		
		if(null != preferentialPlan.getStartDate()){
			statusSql.append("and a.startDate>= ? ");
			countSql.append("and a.startDate>= ? ");
			objectList.add(preferentialPlan.getStartDate());
		}
		
		if(null != preferentialPlan.getEndDate()){
			statusSql.append("and a.startDate<= ? ");
			countSql.append("and a.startDate<= ? ");
			objectList.add(preferentialPlan.getEndDate());
		}
		
		if(null != preferentialPlan.getMinFee()){
			statusSql.append("and a.fee>= ? ");
			countSql.append("and a.fee>= ? ");
			objectList.add(preferentialPlan.getMinFee());
		}
		
		if(null != preferentialPlan.getMaxFee()){
			statusSql.append("and a.fee<= ? ");
			countSql.append("and a.fee<= ? ");
			objectList.add(preferentialPlan.getMaxFee());
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		return page;
	}
	
	@Override
	public Page findTariffByPage(Map<String, Object> map,MedicalTariff tariff,String eid) {
		StringBuilder statusSql = new StringBuilder("from MedicalTariff a where a.eid=? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalTariff a where a.eid=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(admin.getEnterpriseId());
		
		if(StringUtils.isNotBlank(tariff.getTitle())){
			statusSql.append("and a.title like ? ");
			countSql.append("and a.title like ? ");
			objectList.add(tariff.getTitle()+"%");
		}
		
		if(StringUtils.isNotBlank(tariff.getRelayTitle())){
			statusSql.append("and a.relayName like ? ");
			countSql.append("and a.relayName like ? ");
			objectList.add(tariff.getRelayTitle()+"%");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalTariff> list = (List<MedicalTariff>) page.getResult();
		for (MedicalTariff medicalTariff : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), medicalTariff.getType());
			if(null != kv){
				medicalTariff.setTypeValue(kv.getKeyValue());
			}
		}
		return page;
	}
	
	@Override
	public Page findCallRecordByPage(Map<String, Object> map,MedicalCallRecord callRecord,String eid) {
		StringBuilder statusSql = new StringBuilder("from MedicalCallRecord a where a.eid=? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalCallRecord a where a.eid=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(admin.getEnterpriseId());
		
		if(null != callRecord.getCallTime()){
			statusSql.append(" and a.callTime>=? ");
			countSql.append(" and a.callTime>=? ");
			objectList.add(callRecord.getCallTime());
		}
		
		if(null != callRecord.getEndTime()){
			statusSql.append(" and a.endTime<=? ");
			countSql.append(" and a.endTime<=? ");
			objectList.add(callRecord.getEndTime());
		}
		
		if(StringUtils.isNotBlank(callRecord.getExtension())){
			statusSql.append(" and a.extension=? ");
			countSql.append(" and a.extension=? ");
			objectList.add(callRecord.getExtension());
		}
		
		if(StringUtils.isNotBlank(callRecord.getCallNum())){
			statusSql.append(" and a.callNum like ? ");
			countSql.append(" and a.callNum like ? ");
			objectList.add(callRecord.getCallNum()+"%");
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.callTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalCallRecord> list = (List<MedicalCallRecord>) page.getResult();
		for (MedicalCallRecord medicalCallRecord : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), medicalCallRecord.getType());
			medicalCallRecord.setTypeValue(kv.getKeyValue());
		}
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void deletePreferentialPlan(MedicalPreferentialPlan preferentialPlan) {
		String ids[] = preferentialPlan.getId().split(",");
		for(String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalPreferentialPlan m where m.id = ? ", id);
		}
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void deleteTariff(MedicalTariff tariff) {
		String[] ids = tariff.getId().split("-");
		for (String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalTariff a where a.id=? ", id);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updatePreferentialPlan(MedicalPreferentialPlan preferentialPlan) {
		MedicalPreferentialPlan medicalPreferentialPlan = (MedicalPreferentialPlan) this.iBaseDao.getObject(MedicalPreferentialPlan.class, preferentialPlan.getId());
		medicalPreferentialPlan.setTitle(preferentialPlan.getTitle());
		medicalPreferentialPlan.setStartDate(preferentialPlan.getStartDate());
		medicalPreferentialPlan.setEndDate(preferentialPlan.getEndDate());
		medicalPreferentialPlan.setUpdateTime(new Date());
	}

	@Override
	public List<MedicalPreferentialPlan> findUsablePreferentialPlan() {
		// 当前登录用户
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		@SuppressWarnings("unchecked")
		List<MedicalPreferentialPlan> list = this.iBaseDao.getBySql("from MedicalPreferentialPlan a where a.enterpriseId=? and (a.startDate>=CURDATE() or a.endDate>=CURDATE()) ", null, admin.getEnterpriseId());
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalCallRelay> findRelay() {
		List<MedicalCallRelay> list = this.iBaseDao.getBySql("from MedicalCallRelay ", null);
		return list;
	}

	@Override
	public String sumCallFee(MedicalCallRecord callRecord) {
		StringBuilder statusSql = new StringBuilder("select sum(a.fee) from MedicalCallRecord a where a.eid=? ");
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		if(null != callRecord.getCallTime()){
			statusSql.append(" and a.callTime>=? ");
			objectList.add(callRecord.getCallTime());
		}
		
		if(null != callRecord.getEndTime()){
			statusSql.append(" and a.endTime<=? ");
			objectList.add(callRecord.getEndTime());
		}
		
		if(StringUtils.isNotBlank(callRecord.getExtension())){
			statusSql.append(" and a.extension=? ");
			objectList.add(callRecord.getExtension());
		}
		
		if(StringUtils.isNotBlank(callRecord.getCallNum())){
			statusSql.append(" and a.callNum like ? ");
			objectList.add(callRecord.getCallNum()+"%");
		}
		
		BigDecimal sum = (BigDecimal) this.iBaseDao.getObjectByHql(statusSql.toString(), objectList.toArray());
		if(null != sum){
			return sum.toString();
		}else{
			return "";
		}
	}
	
	@Override
	public Workbook exportCallRecordCurrent(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_DATA_RECORD);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] callIds =  ids.split(",");
			BigDecimal decimal = new BigDecimal("0");
			for(int j = 0;j < callIds.length;j++){
				MedicalCallRecord callRecord = (MedicalCallRecord) this.iBaseDao.getObject(MedicalCallRecord.class, callIds[j]);
				if(null != callRecord){
					Row currentRow = sheet.createRow(j + 1);
					currentRow.createCell(0).setCellValue(callRecord.getExtension());
					currentRow.createCell(1).setCellValue(callRecord.getCallNum());
					currentRow.createCell(2).setCellValue(callRecord.getType());
					currentRow.createCell(3).setCellValue(callRecord.getFee().toString());
					currentRow.createCell(4).setCellValue(DateFormatUtils.format(callRecord.getCallTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(5).setCellValue(DateFormatUtils.format(callRecord.getEndTime(),"yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(6).setCellValue(callRecord.getSeconds().toString());
					decimal = decimal.add(callRecord.getFee());
				}
				
			}
			if(callIds.length > 0){
				Row currentRow = sheet.createRow(callIds.length + 1);
				currentRow.createCell(0).setCellValue("合计："+decimal.toString());
			}
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportCallRecordAll(MedicalCallRecord callRecord) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalCallRecord a where a.eid=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(admin.getEnterpriseId());
		
		if(null != callRecord.getCallTime()){
			statusSql.append(" and a.callTime>=? ");
			objectList.add(callRecord.getCallTime());
		}
		
		if(null != callRecord.getEndTime()){
			statusSql.append(" and a.endTime<=? ");
			objectList.add(callRecord.getEndTime());
		}
		
		if(StringUtils.isNotBlank(callRecord.getExtension())){
			statusSql.append(" and a.extension=? ");
			objectList.add(callRecord.getExtension());
		}
		
		if(StringUtils.isNotBlank(callRecord.getCallNum())){
			statusSql.append(" and a.callNum like ? ");
			objectList.add(callRecord.getCallNum()+"%");
		}
		
		List<MedicalCallRecord> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_DATA_RECORD);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		BigDecimal decimal = new BigDecimal("0");
		for(int j = 0;j < size;j++){
			MedicalCallRecord record = list.get(j);
			if(null != record){
				Row currentRow = sheet.createRow(j + 1);
				currentRow.createCell(0).setCellValue(record.getExtension());
				currentRow.createCell(1).setCellValue(record.getCallNum());
				currentRow.createCell(2).setCellValue(record.getType());
				currentRow.createCell(3).setCellValue(record.getFee().toString());
				currentRow.createCell(4).setCellValue(DateFormatUtils.format(record.getCallTime(),"yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(5).setCellValue(DateFormatUtils.format(record.getEndTime(),"yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(6).setCellValue(record.getSeconds().toString());
				decimal = decimal.add(record.getFee());
			}
			
		}
		if(size > 0){
			Row currentRow = sheet.createRow(size + 1);
			currentRow.createCell(0).setCellValue("合计："+decimal.toString());
		}
		return workbook;
	}
	
}
